package com.opentutor.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.opentutor.database.Database;
import com.opentutor.model.Course;

public class CourseDaoImpl implements CourseDao {

	@Override
	public List<Course> getAllCourses() {

		try {
			return getAllCoursesFromDataBase(Database.getConnection());
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void addCourse(Course course) {

		try {
			Connection conn = Database.getConnection();

			PreparedStatement stmt = conn
					.prepareStatement("insert into course(description,name, board,standard, intro_video_link) values(?,?,?,?,?)");
			stmt.setString(1, course.getDesciption());
			stmt.setString(2, course.getName());
			stmt.setString(3, course.getBoard());
			stmt.setString(4, course.getStandard());
			stmt.setString(5, course.getIntroVideoLink());

			stmt.executeUpdate();
		} catch (Exception e) {
		}
	}

	@Override
	public void updateCourse(Course course) {
		try {
			Connection conn = Database.getConnection();

			PreparedStatement stmt = conn
					.prepareStatement("update course set description=?,name = ?, board = ?,standard = ?, updated_date=now(), intro_video_link=? where id=?");
			stmt.setString(1, course.getDesciption());
			stmt.setString(2, course.getName());
			stmt.setString(3, course.getBoard());
			stmt.setString(4, course.getStandard());
			stmt.setString(5, course.getIntroVideoLink());
			stmt.setInt(6, course.getId());

			stmt.executeUpdate();
		} catch (Exception e) {
			
			System.out.println(e.getMessage());
		}
	}

	@Override
	public void deleteCourse(Course course) {
		try {
			Connection conn = Database.getConnection();

			PreparedStatement stmt = conn
					.prepareStatement("delete from course where id=?");
			stmt.setInt(1, course.getId());

			stmt.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

	private List<Course> getAllCoursesFromDataBase(Connection connection)
			throws SQLException {

		PreparedStatement stmt = connection
				.prepareStatement("select * from course");

		ResultSet result = stmt.executeQuery();
		List<Course> courses = new ArrayList<Course>();

		while (result.next()) {
			Course course = getCourse(result);
			courses.add(course);
		}

		return courses;
	}

	private Course getCourse(ResultSet result) throws SQLException {
		Course course = new Course();

		course.setId(result.getInt("id"));
		course.setDesciption(result.getString("description"));
		course.setName(result.getString("name"));
		course.setBoard(result.getString("board"));
		course.setStandard(result.getString("standard"));
		course.setUpdatedDate(result.getDate("updated_date"));
		course.setInsertedDate(result.getDate("inserted_date"));
		course.setIntroVideoLink(result.getString("intro_video_link"));
		
		return course;
	}

	@Override
	public Course getCourseById(int id) {

		try {
			Connection connection = Database.getConnection();
			
			PreparedStatement stmt = connection.prepareStatement("select * from course where id=?");
			stmt.setInt(1, id);

			ResultSet result = stmt.executeQuery();

			if (result.next()) {
				Course course = getCourse(result);
				return course;
			}
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return null;
	}
}
